﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace DelonixRegia.Classes.RoomRecords
{
    public class RoomRecordsDBManager
    {
        public static RoomBooking GetGuestByContactNo(string contactNo)
        {
            RoomBooking rbb = new RoomBooking();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from RoomBooking WHERE contactNo=@contactNo";
                comm.Parameters.AddWithValue("@contactNo", contactNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    rbb.bookingID = (int)dr["bookingID"];
                    rbb.firstName = (string)dr["firstName"];
                    rbb.lastName = (string)dr["lastName"];
                    rbb.noOfAdults = (int)dr["noOfAdults"];
                    rbb.noOfChildren = (int)dr["noOfChildren"];
                    rbb.contactNo = (string)dr["contactNo"];
                    rbb.emailAddress = (string)dr["emailAddress"];
                    rbb.address = (string)dr["address"];
                    rbb.roomNo = (int)dr["roomNo"];
                    rbb.paymentDetails = GetPaymentDetail(Convert.ToInt32(dr["paymentDetails"]));
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return rbb;
        }

        public static RoomBooking GetGuestByName(string name)
        {
            RoomBooking rb = new RoomBooking();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from RoomBooking WHERE concat(firstName, ' ', lastName) LIKE '%' + @name+'%'";
                comm.Parameters.AddWithValue("@name", name);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    rb.bookingID = (int)dr["bookingID"];
                    rb.firstName = (string)dr["firstName"];
                    rb.lastName = (string)dr["lastName"];
                    rb.noOfAdults = (int)dr["noOfAdults"];
                    rb.noOfChildren = (int)dr["noOfChildren"];
                    rb.contactNo = (string)dr["contactNo"];
                    rb.emailAddress = (string)dr["emailAddress"];
                    rb.address = (string)dr["address"];
                    rb.roomNo = (int)dr["roomNo"];
                    rb.paymentDetails = GetPaymentDetail(Convert.ToInt32(dr["paymentDetails"]));
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return rb;
        }


        public static Room GetRoomDetail(int roomNo)
        {
            Room r = new Room();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Room WHERE roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    r.roomNo = (int)dr["roomNo"];
                    r.roomType = (int)dr["roomType"];
                    r.balcony = (bool)dr["balcony"];
                    r.smoking = (bool)dr["smoking"];
                    r.roomLevel = (int)dr["roomLevel"];
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return r;
        }

        public static PaymentDetail GetPaymentDetail(int id)
        {
            PaymentDetail pd = new PaymentDetail();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from PaymentDetail WHERE paymentID=@id";
                comm.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    pd.paymentID = (int)dr["paymentID"];
                    pd.cardType = (string)dr["cardType"];
                    pd.cardholderName = (string)dr["cardholderName"];
                    pd.cardNo = (string)dr["cardNo"];
                    pd.expiryDate = (string)dr["expiryDate"];
                    pd.securityCode = (int)dr["securityCode"];
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return pd;
        }

        public static bool UpdateRoomDetail(Room r)
        {
            int rowschanged = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Room SET roomLevel=@roomLevel, roomType=@roomType, smoking=@smoking, balcony=@balcony WHERE roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomLevel", r.roomLevel);
                comm.Parameters.AddWithValue("@roomType", r.roomType);
                comm.Parameters.AddWithValue("@smoking", r.smoking);
                comm.Parameters.AddWithValue("@balcony", r.balcony);
                comm.Parameters.AddWithValue("@roomNo", r.roomNo);
                rowschanged = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return (rowschanged == 1);
        }

        public static bool UpdateGuest(RoomBooking rb)
        {
            int rowschanged = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE RoomBooking SET firstName=@firstName, lastName=@lastName, noOfAdults=@noOfAdults, noOfChildren=@noOfChildren, contactNo=@contactNo, emailAddress=@emailAddress, address=@address, roomNo=@roomNo WHERE bookingID=@bookingID";
                comm.Parameters.AddWithValue("@firstName", rb.firstName);
                comm.Parameters.AddWithValue("@lastName", rb.lastName);
                comm.Parameters.AddWithValue("@noOfAdults", rb.noOfAdults);
                comm.Parameters.AddWithValue("@noOfChildren", rb.noOfChildren);
                comm.Parameters.AddWithValue("@contactNo", rb.contactNo);
                comm.Parameters.AddWithValue("@emailAddress", rb.emailAddress);
                comm.Parameters.AddWithValue("@address", rb.address);
                comm.Parameters.AddWithValue("@roomNo", rb.roomNo);
                comm.Parameters.AddWithValue("@bookingID", rb.bookingID);
                rowschanged = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return (rowschanged == 1);
        }

        public static bool UpdatePayment(PaymentDetail p)
        {
            int rowschanged = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE PaymentDetail SET cardType=@cardType, cardholderName=@cardholderName, cardNo=@cardNo, expiryDate=@expiryDate, securityCode=@securityCode WHERE paymentID=@paymentID";
                comm.Parameters.AddWithValue("@cardType", p.cardType);
                comm.Parameters.AddWithValue("@cardholderName", p.cardholderName);
                comm.Parameters.AddWithValue("@cardNo", p.cardNo);
                comm.Parameters.AddWithValue("@expiryDate", p.expiryDate);
                comm.Parameters.AddWithValue("@securityCode", p.securityCode);
                comm.Parameters.AddWithValue("paymentID", p.paymentID);
                rowschanged = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return (rowschanged == 1);
        }       
    }
}